package com.creatapark.mapper;

import com.creatapark.entity.MetricEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * ParkSummaryMapper
 *
 * @author Bruce01.fan
 * @date 2017/12/2
 */
@Mapper
public interface ParkSummaryMapper {

    @Results({
            @Result(property = "queryDate",column = "day"),
            @Result(property = "metricValue",column = "total")
    })

    @Select("select a.day,sum(a.total_price) total from (" +
            " select park.day,park.park_hour,count(*) cnt,count(*) * p.price total_price from (" +
            "  select t.*, date_format(in_time, '%Y-%m-%d') day,ROUND(time_to_sec(timediff(out_time,in_time))/3600) park_hour " +
            "  from t_car_inout_data t " +
            "  where t.in_time >= #{staTime} and t.out_time < #{endTime} and park_id=#{parkId}) park," +
            " (select park_id,price from t_park_price where park_id=#{parkId} and price_type=1) p" +
            " where park.park_id = p.park_id and park.park_hour > 0" +
            " group by park.day,park.park_hour" +
            ") a " +
            "group by a.day order by a.day" )
    public List<MetricEntity> queryRevenue(@Param("parkId") Long parkId, @Param("staTime") String staTime, @Param("endTime") String endTime);

    @Results({
            @Result(property = "queryDate",column = "day"),
            @Result(property = "metricValue",column = "total")
    })

    @Select("select p.day,count(*) total from (" +
            " select t.*, date_format(in_time, '%Y-%m-%d') day " +
            " from t_car_inout_data t " +
            " where park_id=#{parkId} " +
            "and (t.in_time >= #{staTime} and t.in_time < #{endTime}) " +
            ") p group by p.day order by p.day asc")
    public List<MetricEntity> queryFlow(@Param("parkId") Long parkId, @Param("staTime") String staTime, @Param("endTime") String endTime);


    @Results({
            @Result(property = "queryDate",column = "day"),
            @Result(property = "metricValue",column = "total")
    })

    @Select("select p.day,count(*) total from" +
            "(select t.*, date_format(in_time, '%Y-%m-%d') day " +
            " from t_car_inout_data t " +
            " where t.in_time >= #{staTime} and t.out_time <= #{endTime} and park_id=#{parkId} " +
            " ) p group by p.day order by p.day asc")
    public List<MetricEntity> queryOrder(@Param("parkId") Long parkId, @Param("staTime") String staTime, @Param("endTime") String endTime);

    /**
     *  平均停车时长
     */
    @Results({
            @Result(property = "queryDate",column = "day"),
            @Result(property = "metricValue",column = "avg_time")
    })
    @Select("select day,ROUND(sum(p.park_hour)/count(*)) avg_time from " +
            "(select date_format(in_time, '%Y-%m-%d') day,ROUND(time_to_sec(timediff(out_time,in_time))/3600) park_hour " +
            "from t_car_inout_data t " +
            "where t.in_time >= #{staTime} and t.out_time <= #{endTime} and park_id=#{parkId} " +
            ") p " +
            " where p.park_hour > 0 " +
            "group by p.day order by p.day asc")
    public List<MetricEntity> queryAvgTime(@Param("parkId") Long parkId, @Param("staTime") String staTime, @Param("endTime") String endTime);

    /**
     *  平均单价
     */
    @Results({
            @Result(property = "queryDate",column = "day"),
            @Result(property = "metricValue",column = "avg_price")
    })
    @Select("select a.day,ROUND(sum(a.total_price)/a.cnt) avg_price from ( " +
            " select park.day,park.park_hour,count(*) cnt,count(*) * p.price total_price from( " +
            "  select t.*, date_format(in_time, '%Y-%m-%d') day," +
            "  ROUND(time_to_sec(timediff(out_time,in_time))/3600) park_hour " +
            "  from t_car_inout_data t " +
            "  where t.in_time >= #{staTime} and t.out_time <= #{endTime} and park_id=#{parkId}) park, " +
            "  (select park_id,price from t_park_price where park_id=1 and price_type=1) p " +
            " where park.park_id = p.park_id and park.park_hour > 0" +
            " group by park.day,park.park_hour" +
            " ) a " +
            "group by a.day order by a.day asc")
    public List<MetricEntity> queryAvgPrice(@Param("parkId") Long parkId, @Param("staTime") String staTime, @Param("endTime") String endTime);

}
